﻿<%@ Page Language="C#" AutoEventWireup="true" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Text" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
	protected void GetTablesButton_Click(object sender, EventArgs e)
	{
		msg.Text = "";
		SqlConnection connection = new SqlConnection(ConnectionStringInput.Text);
		try
		{
			connection.Open();
			try
			{
				string sql = @"SELECT * FROM INFORMATION_SCHEMA.TABLES
														WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME<>'dtproperties'
														ORDER BY TABLE_TYPE, TABLE_NAME";
				SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
				DataSet ds = new DataSet();
				DataTable tb = new DataTable();
				adapter.Fill(tb);

				TableList.DataSource = tb;
				TableList.DataTextField = "TABLE_NAME";
				TableList.DataValueField = "TABLE_NAME";
				TableList.DataBind();
			}
			catch (Exception ex)
			{
				msg.Text = "Get tables error<br>" + ex.ToString();
			}
			finally
			{
				connection.Close();
			}
		}
		catch (Exception ex)
		{
			msg.Text = "Open connection failed.<br>"+ex.ToString();
		}
	}

	protected void GetColumnsButton_Click(object sender, EventArgs e)
	{
		msg.Text = "";
		SqlConnection connection = new SqlConnection(ConnectionStringInput.Text);
		try
		{
			connection.Open();
			try
			{
				string sql = @"SELECT A.name, B.name As typeName, A.length, B.length As sizeLength
														FROM SysColumns A, SysTypes B, SysObjects C
														WHERE A.id=C.id AND A.xtype=B.xtype AND B.name<>'sysname'
																	AND C.name='" + TableList.SelectedValue + "' ORDER BY A.ColOrder";
				
				SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
				DataTable tb = new DataTable();
				adapter.Fill(tb);
				
				ColumnGrid.DataSource = tb;
				ColumnGrid.DataBind();
				//ColumnList.Items.Clear();
				//int itemIndex = 0;
				//foreach (DataRow data in ds.Tables[0].Rows)
				//{
				//  string fieldName = (string)data["name"];
				//  string dataType = (string)data["typeName"];
				//  string fieldSize = "";
				//  if (data["length"] != DBNull.Value && data["sizeLength"] != DBNull.Value)
				//  {
				//    short length = (short)data["length"];
				//    short sizeLength = (short)data["sizeLength"];
				//    if (length != sizeLength)
				//      fieldSize = ((short)data["length"]).ToString();
				//  }
				//  ListViewItem item = new ListViewItem(fieldName, itemIndex);
				//  item.SubItems.Add(dataType);
				//  item.SubItems.Add(fieldSize);
				//  TableStructure.Items.Add(item);
				//  itemIndex++;
				//}
			}
			catch (Exception ex)
			{
				msg.Text = "Get tables error<br>" + ex.ToString();
			}
			finally
			{
				connection.Close();
			}
		}
		catch (Exception ex)
		{
			msg.Text = "Open connection failed.<br>" + ex.ToString();
		}
	}

	protected void GenerateSqlButton_Click(object sender, EventArgs e)
	{
		msg.Text = "";
		SqlConnection connection = new SqlConnection(ConnectionStringInput.Text);
		try
		{
			connection.Open();
			try
			{
				string tableName=TableList.SelectedValue;
				SqlOutPut.Text = "";
				SqlOutPut.Text += "--Generate sql for table: " + tableName;
				SqlOutPut.Text += "\n";
				SqlOutPut.Text += "SET identity_insert " + tableName + " off";
				SqlOutPut.Text += "\n";
				SqlOutPut.Text += "\n";
				SqlOutPut.Text += GenerateTableSql(connection, tableName);
				SqlOutPut.Text += "\n";
				SqlOutPut.Text += "SET identity_insert " + tableName + " ON";			
			}
			catch (Exception ex)
			{
				msg.Text = "Get tables error<br>" + ex.ToString();
			}
			finally
			{
				connection.Close();
			}
		}
		catch (Exception ex)
		{
			msg.Text = "Open connection failed.<br>" + ex.ToString();
		}
	}
	
	string GenerateTableSql(SqlConnection connection, string tableName)
	{
		string sql = @"SELECT * FROM " + tableName;

		SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
		DataTable tb = new DataTable();
		adapter.Fill(tb);

		string fieldTemplate = "";
		int totalColumn = tb.Columns.Count;
		for (int i = 0; i < totalColumn; i++)
		{
			if (tb.Columns[i].ReadOnly)
				continue;

			fieldTemplate += tb.Columns[i].ColumnName + ", ";
		}
		fieldTemplate = fieldTemplate.Trim();
		fieldTemplate = fieldTemplate.Substring(0, fieldTemplate.Length - 1);
		fieldTemplate = "INSERT INTO " + tableName + "(" + fieldTemplate + ") VALUES(";
		StringBuilder sb = new StringBuilder();
		string valueTemp = string.Empty;
		DataRow dr;
		//DataColumn c;
		//sb.Append("SET identity_insert " + tableName + " off");
		//sb.Append("\n");
		for (int i = 0; i < tb.Rows.Count; i++)
		{
			valueTemp = "";
			dr = tb.Rows[i];
			//build values
			for (int j = 0; j < totalColumn; j++)
			{
				if (tb.Columns[j].ReadOnly)
					continue;
				//check null field
				if (DBNull.Value == dr[j])
					valueTemp += "NULL, ";
				else
					valueTemp += "'" + dr[j].ToString().Trim() + "', ";
			}
			//strip value
			valueTemp = valueTemp.Trim();
			valueTemp = valueTemp.Substring(0, valueTemp.Length - 1);

			sb.Append(fieldTemplate + valueTemp + ")");
			sb.Append("\n");
		}
		return sb.ToString();
	}

	protected void GenerateAllTableSqlButton_Click(object sender, EventArgs e)
	{
		msg.Text = "";
		SqlConnection connection = new SqlConnection(ConnectionStringInput.Text);
		try
		{
			connection.Open();
			try
			{
				string tableName = "";
				
				SqlOutPut.Text = "";
				SqlOutPut.Text += "SET identity_insert " + tableName + " off";
				SqlOutPut.Text += "\n";
				StringBuilder sb = new StringBuilder();
				if (GenerateSelectedTableCheck.Checked)
				{
					var selectedTables = TableList.GetSelectedIndices();
					for (int i = 0; i < selectedTables.Length; i++)
					{
						tableName = TableList.Items[selectedTables[i]].Value;
						sb.Append("--Generate sql for table: " + tableName);
						sb.Append("\n");
						sb.Append(GenerateTableSql(connection, tableName));
					}
				}
				else
				{
					for (int i = 0; i < TableList.Items.Count; i++)
					{
						tableName = TableList.Items[i].Value;
						sb.Append("--Generate sql for table: " + tableName);
						sb.Append("\n");
						sb.Append(GenerateTableSql(connection, tableName));
					}
				}

				SqlOutPut.Text += sb.ToString();
				SqlOutPut.Text += "\n";
				SqlOutPut.Text += "SET identity_insert " + tableName + " ON";
			}
			catch (Exception ex)
			{
				msg.Text = "Get tables error<br>" + ex.ToString();
			}
			finally
			{
				connection.Close();
			}
		}
		catch (Exception ex)
		{
			msg.Text = "Open connection failed.<br>" + ex.ToString();
		}
	}

	protected void ExecSqlButton_Click(object sender, EventArgs e)
	{
		msg.Text = "";
		SqlConnection connection = new SqlConnection(ConnectionStringInput.Text);
		try
		{
			connection.Open();
			try
			{
				string sql=SqlOutPut.Text.Trim();
				string sqlTemp = sql.Substring(0, 10).ToUpper();
				if (sqlTemp.ToUpper().StartsWith("SELECT"))
				{
					SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
					DataTable tb = new DataTable();
					adapter.Fill(tb);
					ColumnGrid.DataSource = tb;
					ColumnGrid.DataBind();
				}
				else
				{
					var cmd = new SqlCommand(sql, connection);
					msg.Text = "Result: " + cmd.ExecuteScalar().ToString();
				}
			}
			catch (Exception ex)
			{
				msg.Text = "Get tables error<br>" + ex.ToString();
			}
			finally
			{
				connection.Close();
			}
		}
		catch (Exception ex)
		{
			msg.Text = "Open connection failed.<br>" + ex.ToString();
		}
	}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
	<title>Simple SQL Dumper - Y: duongdragon05</title>
</head>
<body>
	<center>
		<form id="form1" runat="server">
		<div style="text-align: left; width:800px;">
			<table width="100%">
				<tr>
					<td>
						Connection string
						<asp:TextBox ID="ConnectionStringInput" runat="server" Width="100%" Text="server=.\sql05;database=DaMaiVietTrung;uid=sa;pwd=sa@123-=;" />
					</td>
				</tr>
				<tr>
					<td>
						<asp:Button ID="GetTablesButton" runat="server" Text="Get table(s)" OnClick="GetTablesButton_Click" />
						<asp:Button ID="GetColumnsButton" runat="server" Text="Get column(s)" OnClick="GetColumnsButton_Click" />
						<asp:Button ID="GenerateSqlButton" runat="server" Text="Gen SQL" OnClick="GenerateSqlButton_Click" />
						<asp:Button ID="ExecSqlButton" runat="server" Text="Exec Sql" OnClick="ExecSqlButton_Click" />
					</td>
				</tr>
				<tr>
					<td>
						<asp:Button ID="GenerateAllTableSqlButton" runat="server" Text="Gen SQL to All table"
							OnClick="GenerateAllTableSqlButton_Click" />
						<asp:CheckBox ID="GenerateSelectedTableCheck" runat="server" Text="Sinh sql cho những bảng được chọn"
							Checked="true" />
					</td>
				</tr>
				<tr>
					<td>
						<asp:Label ID="msg" runat="server" ForeColor="Blue" Font-Bold="true" />
					</td>
				</tr>
				<tr>
					<td>
						<table width="100%">
							<tr>
								<td width="250px">
									Bảng
								</td>
								<td>
									Cột
								</td>
							</tr>
							<tr>
								<td valign="top">
									<asp:ListBox runat="server" ID="TableList" Width="100%" Height="200px" SelectionMode="Multiple" />
								</td>
								<td valign="top">
									<div style="width: 550px; height: 200px; overflow: scroll">
										<asp:DataGrid ID="ColumnGrid" runat="server" /></div>
								</td>
							</tr>
							<tr>
								<td valign="top" colspan="2">
									Sql
									<asp:TextBox ID="SqlOutPut" runat="server" TextMode="MultiLine" Width="100%" Rows="20" />
								</td>
							</tr>
						</table>
					</td>
				</tr>
				<tr>
					<td style="font-size: 12px; font-style: italic; color: Gray;">
						- Hệ thống sẽ tự động thêm lệnh tắt chế độ tự tăng của trường tự động tăng
						<br />
						- Cần tạo bảng trước khi chạy lệnh được gen ra
						<br />
						- Chỉ nên sử dụng cho bảng dữ liệu nhỏ
						<br />
						- Mỗi lần tạo SQL cho 1 table
						<br />
						- Lệnh SELECT, lấy dữ liệu bảng đơn, dữ liệu hiển thị ở bảng dùng để hiển thị cột
						(có thể vỡ giao diện)
						<br />
						Cái này do ngẫu hứng làm ra nên mong pà kon có chém thì nhẹ tay 1 tí :D
						<br />
						Mọi thắc mắc xin liên hệ:
						<br />
						- Nguyễn Bá Thái Bình Dương
						<br />
						- Y: duongdragon05
						<br />
						- Blog: <a href="http://duongnguyen.giacatluong.com/" title="Vì một tương lai tươi sáng">http://duongnguyen.giacatluong.com/</a>
					</td>
				</tr>
			</table>
		</div>
		</form>
	</center>
</body>
</html>
